The master or template set is retained in the facility’s desktop as
well as in the facility’s network drive.
The entry spreadsheets are organized to mimic the old system, however
with most spreadsheets changed as tabs‐within‐spreadsheets to increase
speed and minimize link breakages. Data connections are set up across
spreadsheets to minimize manual copy‐and‐paste and transcription errors.
The precedence of opened spreadsheets is critical to proper data
retrieval, with dependencies shown below:
green = needs to be uploaded to database
blue = keep archived for records but not included in
database
drag nodes as needed to clarify linkage view
Detailed procedures (Appendix E) were provided to the lead
operators.
In‐House Data
In‐house data entry was structured to minimize transformation required
to upload to the database. Automation of cleaning was preferred due to
the sheer volume of tabs the operator would otherwise have to manually
repeat the process through.
Following the routine monthly totals verification, the procedure instructs the lead operator to create a copy of the checked data entry files into the network drive to avoid accidentally overwriting earlier data from the season, as the for‐upload data will be trimmed to only dates not already within the database.
There are two Excel macros to be run – i) a date trimmer, wherein the operator selects the start and end date of the data to be uploaded (e.g. starting from where the last upload ended) and ii) a csv convertor, which cycles through and flattens the necessary tabs of the data entry spreadsheet to csv files.
The operator may then check the staged csv files again (the macros do not catch or eliminate stray columns or rows which may have been inadvertently introduced during data entry) in the staging folder. The Access database then has an import macro to collect all the csv files present in the staging folder.
The flow of entry spreadsheets & (selected) tabs to database table is illustrated in Figure 2. In general, spreadsheets/tabs purposed for summary reporting (e.g. monthly reports and sheet reconciliation) spreadsheet and calculators (e.g. the pump guides) are kept out of the database, as the values can be calculated from raw data within the database. This is aimed to conserve space in the database.
Reorganized dataflow
Spreadsheet -> Tab/Table Name -> Database Table ->
Joined/Queried Database Table
gray = not uploaded to database, retained in local archive
drag nodes as needed to clarify linkage view